﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace Excel16AddIn_Z01
{
    public partial class Form1 : Form
    {
        static Form1 form1 = null;

        public static void AppendMsg(string _str)
        {
            if (form1 != null)
            {
                form1.tbMsg.AppendText(_str + "\r\n");
                //form1.tbMsg
            }
        }

        public Form1()
        {
            InitializeComponent();

            form1 = this;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.Text = Globals.ThisAddIn.Application.ActiveWorkbook.Name;
            AppendMsg("Form1_Load(...) : "+ this.Text);
        }

        private void btnLocalExcelInfo_Click(object sender, EventArgs e)
        {
            tbMsg.AppendText(Globals.ThisAddIn.Application.ActiveWorkbook.FullName+"\r\n");
            tbMsg.AppendText("\r\n");
        }

        private void btnOpenExcel01_Click(object sender, EventArgs e)
        {
            Globals.ThisAddIn.Application.Workbooks.Open(@"E:\Gitee\vsto\Excel_Test\测试1.xlsx");
        }

        private void btnOpenExcel02_Click(object sender, EventArgs e)
        {
            Globals.ThisAddIn.Application.Workbooks.Open(@"G:\Excel_Test\2018年发货总表.xlsx");
        }

        private void btnEveryCell01_Click(object sender, EventArgs e)
        {
            //Excel.Worksheet sheet = Globals.ThisAddIn.Application.ThisWorkbook.ActiveSheet;
            Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
            tbMsg.AppendText("sheet.Rows.Count: " + sheet.Rows.Count+"\r\n");
            tbMsg.AppendText("sheet.Columns.Count: " + sheet.Columns.Count+"\r\n");

            // VSTO 获取sheet单元格行列数 - zengzongfa - 博客园.html(https://www.cnblogs.com/igor/p/4241561.html)
            //for (int r = 1; r < 9; r++)
            //{
            //    for (int c = 1; c < 9; c++)
            //        //tbMsg.AppendText(Globals.ThisAddIn.Application.Cells[r, c].Value);
            //        if (sheet.Cells[r, c] != null)
            //            tbMsg.AppendText(sheet.Cells[r, c].Value);
            //}

            //Dim Dtsheet As Excel.Worksheet
            //Dim TotalC As Long             '原始数据范围列
            //Dim TotalR As Long             '原始数据范围行

            //Excel.Worksheet Dtsheet = Globals.ThisAddIn.Application.ThisWorkbook.Sheets["Sheet1"];
            Excel.Worksheet Dtsheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["Sheet1"];
            int TotalC = Dtsheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
            int TotalR = Dtsheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
            tbMsg.AppendText("TotalC: " + TotalC + "\r\n");
            tbMsg.AppendText("TotalR: " + TotalR + "\r\n");
            tbMsg.AppendText(Dtsheet.Cells[0, 0].Value + "\r\n");
            //Dtsheet.Range(
            //    "A1",
            //    Dtsheet.Cells(TotalR, TotalC))
            //    .Replace(
            //        What:= "",
            //        Replacement:= "N/A",
            //        LookAt:= Excel.XlLookAt.xlWhole,
            //        SearchOrder:= Excel.XlSearchOrder.xlByRows,
            //        MatchCase:= False,
            //        SearchFormat:= False,
            //        ReplaceFormat:= False)

        }

        private void btnEveryCell02_Click(object sender, EventArgs e)
        {
            // ZC: Cells的行列序号 是从1开始的...
            Excel.Worksheet Dtsheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["Sheet1"];
            tbMsg.AppendText("A \r\n");
            tbMsg.AppendText(Dtsheet.Cells[1, 2].Value + "\r\n");
            tbMsg.AppendText(Dtsheet.Cells[1, 3].Value + "\r\n");
            tbMsg.AppendText(Dtsheet.Cells[2, 2].Value + "\r\n");
            tbMsg.AppendText(Dtsheet.Cells[2, 3].Value + "\r\n");
            tbMsg.AppendText(Dtsheet.Cells[3, 2].Value + "\r\n");
            tbMsg.AppendText(Dtsheet.Cells[3, 3].Value + "\r\n");
            tbMsg.AppendText("B \r\n");

            //AppendMsg("Dtsheet.Range.Column: " + Dtsheet.ar.Column);
            //AppendMsg("Dtsheet.Range.Column: " + Dtsheet.Range.Column);
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            form1 = null;
            GlobalParam.form1 = null;
        }

        private void Form1_Shown(object sender, EventArgs e)
        {
            //this.Text = Globals.ThisAddIn.Application.ActiveWorkbook.Name;
            AppendMsg("Form1_Shown(...) : " + Globals.ThisAddIn.Application.ActiveWorkbook.Name);
        }

        private void Form1_Activated(object sender, EventArgs e)
        {
            AppendMsg("Form1_Activated(...) : " + Globals.ThisAddIn.Application.ActiveWorkbook.Name);

        }

        private void Form1_Leave(object sender, EventArgs e)
        {
        }

        private void btnOpenedExcel_Click(object sender, EventArgs e)
        {
            // 下面这个 序号也是从1开始的...
            int iCnt = Globals.ThisAddIn.Application.Workbooks.Count;
            AppendMsg("iCnt: "+ iCnt);
            //AppendMsg(Globals.ThisAddIn.Application.Workbooks.Item[1].FullName);
            for (int i = 1; i <= iCnt; i++)
                AppendMsg(Globals.ThisAddIn.Application.Workbooks.Item[i].FullName);
        }

        private void btnActivateExcel1_Click(object sender, EventArgs e)
        {
            Globals.ThisAddIn.Application.Workbooks.Item[1].Activate();
        }

        private void btnActivateExcel2_Click(object sender, EventArgs e)
        {
            Globals.ThisAddIn.Application.Workbooks.Item[2].Activate();
        }

        private void btnCellProp_Click(object sender, EventArgs e)
        {
            Excel.Worksheet Dtsheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["Sheet1"];

            AppendMsg("");
            Excel.Range rng;// 声明一个Excel的单元格区域变量
            rng = Globals.ThisAddIn.Application.Range["b1"];//.Selection;// 获得Excel所选区域
            if (rng.MergeCells)
                AppendMsg("b1 is MergeCells");
            else
                AppendMsg("b1 is not MergeCells");
            foreach (Excel.Range area in rng.Areas)
            {
                AppendMsg(String.Format("Area address is {0}",
                  area.get_Address(null, null,
                  Excel.XlReferenceStyle.xlA1, null, null)));
            }
            AppendMsg("rng.MergeArea.Address： "+ rng.MergeArea.Address);
            AppendMsg("Dtsheet.Cells[1, 2].MergeArea.Count : " + Dtsheet.Cells[1, 2].MergeArea.Count);

            AppendMsg("");
            rng = Globals.ThisAddIn.Application.Range["b4"];//.Selection;// 获得Excel所选区域
            if (rng.MergeCells)
                AppendMsg("b4 is MergeCells");
            else
                AppendMsg("b4 is not MergeCells");
            foreach (Excel.Range area in rng.Areas)
            {
                AppendMsg(String.Format("Area address is {0}",
                  area.get_Address(null, null,
                  Excel.XlReferenceStyle.xlA1, null, null)));
            }
            AppendMsg("rng.MergeArea.Address： " + rng.MergeArea.Address);
            AppendMsg("Dtsheet.Cells[3, 3].MergeArea.Count : " + Dtsheet.Cells[3, 3].MergeArea.Count);

            AppendMsg("");
            rng = Globals.ThisAddIn.Application.Range["b1", "b3"];
            AppendMsg("rng.Address： " + rng.Address);

            //rng = Globals.ThisAddIn.Application.Selection;
            //rng.ro
            //(Selection.Information(wdEndOfRangeRowNumber) - _
            //Selection.Information(wdStartOfRangeRowNumber)) +1
        }

        private void btmExcelTopmost_Click(object sender, EventArgs e)
        {
            //WinApiUtil.SetWindowPos(this.Handle, (IntPtr)WinApiUtil.HWND_TOPMOST, 0, 0, 0, 0, WinApiUtil.SWP_NOSIZE | WinApiUtil.SWP_NOMOVE);
            WinApiUtil.SetWindowPos((IntPtr)Globals.ThisAddIn.Application.Hwnd,
                (IntPtr)WinApiUtil.HWND_TOPMOST, 0, 0, 0, 0, WinApiUtil.SWP_NOSIZE | WinApiUtil.SWP_NOMOVE);
        }

        private void btmExcelNoTopmost_Click(object sender, EventArgs e)
        {
            //WinApiUtil.SetWindowPos(this.Handle, (IntPtr)WinApiUtil.HWND_NOTOPMOST, 0, 0, 0, 0, WinApiUtil.SWP_NOSIZE | WinApiUtil.SWP_NOMOVE);
            WinApiUtil.SetWindowPos((IntPtr)Globals.ThisAddIn.Application.Hwnd,
                (IntPtr)WinApiUtil.HWND_NOTOPMOST, 0, 0, 0, 0, WinApiUtil.SWP_NOSIZE | WinApiUtil.SWP_NOMOVE);
        }

        private void btnRange_Click(object sender, EventArgs e)
        {
            AppendMsg("");

            Excel.Worksheet Dtsheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets["Sheet1"];
            Excel.Range rng = Globals.ThisAddIn.Application.Selection;// 获得Excel所选区域

            rng = Globals.ThisAddIn.Application.Range["b1"];//.Selection;// 获得Excel所选区域
            foreach (Excel.Range area in rng.Areas)
            {
                AppendMsg(String.Format("Area address is {0}",
                  area.get_Address(null, null,
                  Excel.XlReferenceStyle.xlA1, null, null)));
            }
            AppendMsg("rng.MergeArea.Address： " + rng.MergeArea.Address);
            AppendMsg("Dtsheet.Cells[3, 3].MergeArea.Count : " + Dtsheet.Cells[3, 3].MergeArea.Count);

            AppendMsg("");
            rng = Globals.ThisAddIn.Application.Range["b1", "b3"];
            AppendMsg("rng.Address： " + rng.Address);
        }

        private void btnExcelScroll_Click(object sender, EventArgs e)
        {
            //Globals.ThisAddIn.Application.ActiveWindow.ScrollColumn += 2;
            //Globals.ThisAddIn.Application.ActiveWindow.ScrollRow += 2;

            // ZC: LargeScroll(...) 貌似移动幅度 有些过大
            //Globals.ThisAddIn.Application.ActiveWindow.LargeScroll(2, 0, 2, 0);
            Globals.ThisAddIn.Application.ActiveWindow.SmallScroll(2, 0, 2, 0);
        }

        private void btnExcelScroll01_Click(object sender, EventArgs e)
        {
            //// ZC: 下面这个设置，减过头变成负值的话 会异常
            //Globals.ThisAddIn.Application.ActiveWindow.ScrollColumn -= 5;
            //Globals.ThisAddIn.Application.ActiveWindow.ScrollRow -= 5;

            //Globals.ThisAddIn.Application.ActiveWindow.LargeScroll(0,5,0,5);
            // ZC: SmallScroll(...) 移动幅度不会太大，而且"减过头变成负值" 也不会异常
            Globals.ThisAddIn.Application.ActiveWindow.SmallScroll(0, 5, 0, 5);
        }

        private void btnNewExcel_Click(object sender, EventArgs e)
        {
            // ZC: 这样创建的新文件，会立即打开显示出来
            string strNew = @"C:\test11.xlsx";
            Excel.Workbook wb = Globals.ThisAddIn.Application.Workbooks.Add();
            //wb.Close();
            wb.SaveAs(strNew);
        }

        static int SCROLL_LENGTH = 30;
        Keys keySpecial = Keys.Control;// 也就 Ctrl和Shift效果好一点...

        // 这是对整个界面的快捷键的设置，比如查询，保存。
        protected override bool ProcessCmdKey(ref Message msg, Keys keyData)
        {
            // ZC: 使用 this.HorizontalScroll的效果很不好，一调用this.PerformLayout();界面就定位到获取焦点的控件上去了
            // ZC: 现在使用 panel1.HorizontalScroll和panel1.PerformLayout();就避免了这个问题

            //AppendMsg("Control.ModifierKeys: " + Control.ModifierKeys + "  keyData: " + keyData);
            if ((Control.ModifierKeys & keySpecial) != 0 && keyData == (Keys.A | keySpecial))
            {
                int iNew = panel1.HorizontalScroll.Value - SCROLL_LENGTH;
                if (iNew < 0) iNew = 0;
                panel1.HorizontalScroll.Value = iNew;
                panel1.PerformLayout();
                return true;//这里return true 否则控件焦点会跟着方向键改变
            }
            else if ((Control.ModifierKeys & keySpecial) != 0 && keyData == (Keys.W | keySpecial))
            {
                int iNew = panel1.VerticalScroll.Value - SCROLL_LENGTH;
                if (iNew < 0) iNew = 0;
                panel1.VerticalScroll.Value = iNew;
                panel1.PerformLayout();
                return true;//这里return true 否则控件焦点会跟着方向键改变
            }
            else if ((Control.ModifierKeys & keySpecial) != 0 && keyData == (Keys.D | keySpecial))
            {
                int iNew = panel1.HorizontalScroll.Value + SCROLL_LENGTH;
                if (iNew > panel1.HorizontalScroll.Maximum) iNew = HorizontalScroll.Maximum;
                panel1.HorizontalScroll.Value = iNew;
                panel1.PerformLayout();
                return true;//这里return true 否则控件焦点会跟着方向键改变
            }
            else if ((Control.ModifierKeys & keySpecial) != 0 && keyData == (Keys.S | keySpecial))
            {
                int iNew = panel1.VerticalScroll.Value + SCROLL_LENGTH;
                if (iNew > panel1.VerticalScroll.Maximum) iNew = VerticalScroll.Maximum;
                panel1.VerticalScroll.Value = iNew;
                panel1.PerformLayout();
                return true;//这里return true 否则控件焦点会跟着方向键改变
            }
            return base.ProcessCmdKey(ref msg, keyData);//这句保证其它按键能正常使用
        }

        private void btnFormScroll1_Click(object sender, EventArgs e)
        {
            
        }

        private void btnFormScroll2_Click(object sender, EventArgs e)
        {
            
        }


    }
}
